
DECLARE	@x XML

-- build some xml
SET @x = (
	SELECT TABLE_SCHEMA
		, TABLE_NAME
		, (
			SELECT TABLE_SCHEMA AS '@table_schema'
				, TABLE_NAME AS '@table_name'
				, COLUMN_NAME
				, ORDINAL_POSITION
				, COLUMN_DEFAULT
				, IS_NULLABLE
				, DATA_TYPE
				, CHARACTER_MAXIMUM_LENGTH
				, NUMERIC_PRECISION
				, NUMERIC_SCALE
				, DATETIME_PRECISION
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE ISNULL(TABLE_SCHEMA, '') = ISNULL(t.TABLE_SCHEMA, '')
			AND TABLE_NAME = t.TABLE_NAME
			ORDER BY ORDINAL_POSITION -- make document order match the ordinal_position
			FOR XML PATH('column_element'), TYPE
		)
	FROM INFORMATION_SCHEMA.TABLES t
	ORDER BY TABLE_NAME
	FOR XML PATH('table_element'), TYPE
)

-- parse xml
SELECT column_element.value('(/column_element/@table_schema)[1]', 'NVARCHAR(128)') AS table_schema
	, column_element.value('(/column_element/@table_name)[1]', 'NVARCHAR(128)') AS table_name
	, column_element.value('(/column_element/COLUMN_NAME)[1]', 'NVARCHAR(128)') AS column_name
	, column_element.value('(/column_element/ORDINAL_POSITION)[1]', 'INT') AS ordinal_position
	, document_order
	, CASE WHEN column_element.value('(/column_element/ORDINAL_POSITION)[1]', 'INT') = document_order
		THEN 1 ELSE 0 END AS orders_match
FROM (
	SELECT seq.n AS document_order
		, t2.c.query('.') AS column_element
	FROM (SELECT CAST(n AS INT) AS n FROM ssf.uf_sequence_number(30000)) AS seq -- max number of columns in a table is 30k
	CROSS APPLY @x.nodes('/table_element/column_element[position() = sql:column("seq.n")]/.') AS t1(c)
	CROSS APPLY t1.c.nodes('.') AS t2(c)
	WHERE seq.n <= @x.query('count(/table_element/column_element)').value('.', 'int')
) x
ORDER BY table_name
	, ordinal_position
